Database Migration

Following are the checklist before migration process.
Database Migration Checklist

---

Pre-Migration Checklist

1. Analyze the disk space of the target server for the new database, if the disk space is not enough add more space on the target server
2. Confirm the data and log file location for the target server
3. Collect the information about the Database properties (Auto Stats, DB Owner, Recovery Model, Compatibility level, Trustworthy option etc)
4. Collect the information of dependent applications, make sure application services will be stopped during the database migration
5. Collect the information of database logins, users and their permissions. (Optional)
6. Check the database for the Orphan users if any
7. Check the SQL Server for any dependent objects (SQL Agent Jobs and Linked Servers)
8. Check, if the database is part of any maintenance plan

---

Below are various scripts you can run to collect data.
Script to Check the Disk and Database Size

-- Procedure to check disc space
exec master..xp_fixeddrives
-- To Check database size
exec sp_helpdb [dbName]
or
use [dbName]
select str(sum(convert(dec(17,2),size)) / 128,10,2)  + 'MB'
from dbo.sysfiles
GO

Script to Check Database Properties

select 
 sysDB.database_id,
 sysDB.Name as 'Database Name',
 syslogin.Name as 'DB Owner',
 sysDB.state_desc,
 sysDB.recovery_model_desc,
 sysDB.collation_name, 
 sysDB.user_access_desc,
 sysDB.compatibility_level, 
 sysDB.is_read_only,
 sysDB.is_auto_close_on,
 sysDB.is_auto_shrink_on,
 sysDB.is_auto_create_stats_on,
 sysDB.is_auto_update_stats_on,
 sysDB.is_fulltext_enabled,
 sysDB.is_trustworthy_on
from sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

Script to List Orphan Users

sp_change_users_login 'report'
GO

Script to List Linked Servers

select  *
from sys.sysservers

Script to List Database Dependent Jobs

select 
 distinct 
 name,
 database_name
from sysjobs sj
INNER JOIN sysjobsteps sjt on sj.job_id = sjt.job_id

---